package com.apes.fn.server.servicePlan.repository;

import com.apes.fn.crm.customerExpand.FnCustomerExpand;
import com.apes.fn.server.servicePlan.model.ServicePlanItem;
import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.hr.base.model.Employee;
import com.apes.scm.masterdata.frame.model.Dept;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

/**
 * param:zyc
 **/
public interface ServicePlanItemRepository extends CustomRepository<ServicePlanItem, String> {

    @Query(value = "select f from ServicePlanItem f where f.serviceState = true " +
            "and f.product.productGroup.id = '10' and f.saleQty > (f.qtyReceive + f.qtyEnd) " +
            "and f.location.id = :locationId and f.servicePlan.id = :orderId ")
    List<ServicePlanItem> findServicePlanItemsForPDA(@Param("orderId") String orderId, @Param("locationId") String locationId);

    @Query(value = "select f from ServicePlanItem f left join MaintenanceOrder g on g.servicePlanItem.id = f.id where f.servicePlan.belongDept = :dept and f.servicePlan.fnCustomerExpand = :fnCustomerExpand " +
            " and f.assistType.id = 'ServicePlanItem.assistType.normal' and f.serviceState = true and f.terminationDate is null " +
            " and g.id is not null and g.lockState = false and g.workPlatform in ('cosmetology','machineRepa') ")
    List<ServicePlanItem> findNeedWorkingServicePlan(@Param("dept") Dept dept, @Param("fnCustomerExpand") FnCustomerExpand fnCustomerExpand);

    @Query(value = "select f from ServicePlanItem f left join MaintenanceOrder g on g.servicePlanItem.id = f.id where g.id = :mid " )
    List<ServicePlanItem> findPlanByModer(@Param("mid") String mid);

    @Query("SELECT T FROM ServicePlanItem T WHERE T.product.id = :productId AND (T.servicePlan.id = :source OR T.servicePlan.id = :origin)")
    ServicePlanItem findByServicePlanIdAndProductId(@Param("source") String source,@Param("origin") String origin,@Param("productId") String productId);

    List<ServicePlanItem> findByRelevanceWorkOrder(String relevanceWorkOrder);

    @Query(value = "SELECT B.id, B.primary_price_unit_show, B.product_id " +
            "FROM SER_SERVICE_PLAN A, " +
            "     SER_SERVICE_PLAN_ITEM b " +
            "WHERE B.service_plan_id = A.ID " +
            "  AND A.STATE IN ('create', 'submission') " +
            "  AND B.PRODUCT_ID = :productId " +
            "  AND B.primary_price_unit_show != :amount " +
            "  AND A.CREATE_DATE > add_months(sysdate, -3) " +
            "  AND A.car_liscence = :carNumber " +
            "  AND (B.ID <> nvl(:id, ' '))", nativeQuery = true)
    List<Map> findShowPriceUnitTips(@Param("productId") String productId, @Param("amount") double amount,
                                    @Param("carNumber") String carNumber, @Param("id") String id);
    @Query(value = "SELECT B.id, B.primary_price_unit_show, B.product_id " +
            "FROM SER_SERVICE_PLAN A, " +
            "     SER_SERVICE_PLAN_ITEM b " +
            "WHERE B.service_plan_id = A.ID " +
            "  AND A.STATE IN ('create', 'submission') " +
            "  AND B.PRODUCT_ID in :productIds " +
            "  AND A.CREATE_DATE > add_months(sysdate, -3) " +
            "  AND A.car_liscence = :carNumber ", nativeQuery = true)
    List<Map> findShowPriceUnitTips(@Param("productIds") List productIds,
                                    @Param("carNumber") String carNumber);

    @Query(value = "" +
            " SELECT CARRIER_ID, CARRIER_NAME, SERVICE_PLAN_ID FROM SER_SERVICE_PLAN_ITEM A  WHERE OLD_INQUIRY_NUMBER = ?1 AND PRODUCT_ID = ?2 AND SERVICE_PLAN_ID = ?3 " +
            " UNION ALL " +
            " SELECT CARRIER_ID, CARRIER_NAME, LOSS_QUOTATION_ID FROM SER_LOSS_QUOTATION_ITEM WHERE LOSS_QUOTATION_ID = ?3 AND OLD_INQUIRY_NUMBER = ?1 AND PRODUCT_ID = ?2 ", nativeQuery = true)
    List<Map> findCarrier(String commodInquireId, String productId, String servicePlanId);

    @Query(value = "SELECT OLD_INQUIRY_NUMBER, UNIQUE_VALUE FROM SER_SERVICE_PLAN_ITEM T WHERE SERVICE_PLAN_ID = ?1 AND OLD_INQUIRY_NUMBER = ?2 " +
            " UNION ALL " +
            " SELECT OLD_INQUIRY_NUMBER, UNIQUE_VALUE FROM SER_LOSS_QUOTATION_ITEM WHERE LOSS_QUOTATION_ID = ?1 AND OLD_INQUIRY_NUMBER = ?2  ", nativeQuery = true)
    List<Map> findLockInquir(String servicePlanId, String oldInquiryNumber);

    @Query(value = "select a.price_cost_unit_ex_tax P001, a.SUPPLIER_ID SUPPLIER_ID, a.UNIQUE_VALUE uniqueValue from ser_service_plan_item a where a.service_plan_id = :id and a.product_id = :product and a.supplier_id = :supplier" +
            " UNION " +
            "select a.price_cost_unit_ex_tax P001, a.SUPPLIER_ID SUPPLIER_ID, a.UNIQUE_VALUE uniqueValue from ser_loss_quotation_item a where a.loss_Quotation_id = :id and a.product_id = :product and a.supplier_id = :supplier", nativeQuery = true)
    Map findByPrice(String id, String product, String supplier);

}
